﻿using UnityEngine;
using System.Collections;
using System.Data;
using System;
using MySql.Data.MySqlClient;
using MySql.Data;
using System.IO;

public class SqlAccess {

    public static MySqlConnection dbConnection;

    //如果只是在本地的话，写localhost就可以。
    static string host = "localhost";

    //如果是局域网，那么写上本机的局域网IP；
    //static string host = "192.168.14.177";

    static string id = "root";
    static string pwd = "root";
    static string database = "login";

    public SqlAccess()
    {
        OpenSql();
    }

    public static void OpenSql()
    {
        try
        {
            string connectionString = string.Format("Server = {0};port = {4};Database={1};UserID = {2};Password={3};", host, database, id, pwd, "3306");
            dbConnection = new MySqlConnection(connectionString);
            dbConnection.Open();

        }
        catch (Exception e)
        {
            throw new Exception ("服务器连接失败，请重新检查是否打开Myaql服务。"+ e.Message.ToString ());
        }
    }

    public DataSet CreateTable(string name,string[] col,string[] colType)
    {
        if (col.Length != colType .Length)
        {
            throw new Exception("colums.Length != colType.Length");
        }

        string query = "CREATE TABLE" + name + "(" + col[0] + "" + colType[0];

        for (int i = 0; i < col.Length; ++i)
        {
            query += "," + col[i] + " " + colType[i];
        }

        query += ")";
        return ExecuteQuery(query);
    }

    public DataSet CreateTableAutoID(string name,string[] col,string[] colType)
    {
        if (col.Length != colType.Length)
        {
            throw new Exception("columns.Length != colType.Length");
        }

        string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0] + " NOT NULL AUTO_INCREMENT";

        for (int i = 1; i < col.Length; ++i)
        {
            query += ", " + col[i] + " " + colType[i];
        }
        query += ", PRIMARY KEY (" + col[0] + ")" + ")";
        Debug.Log(query);
        return ExecuteQuery(query);
    }

    //插入一条数据，包括所有，不适用自动累加ID。
    public DataSet InsertInto(string tableName, string[] values)
    {
        string query = "INSERT INTO " + tableName + " VALUES (" + "'" + values[0] + "'";
        for (int i = 1; i < values.Length; ++i)
        {
            query += ", " + "'" + values[i] + "'";
        }
        query += ")";
        Debug.Log(query);
        return ExecuteQuery(query);
    }

    //插入部分ID
    public DataSet InsertInto(string tableName, string[] col, string[] values)
    {
        if (col.Length != values.Length)
        {
            throw new Exception("columns.Length != colType.Length");
        }
        string query = "INSERT INTO " + tableName + " (" + col[0];
        for (int i = 1; i < col.Length; ++i)
        {
            query += ", " + col[i];
        }
        query += ") VALUES (" + "'" + values[0] + "'";
        for (int i = 1; i < values.Length; ++i)
        {
            query += ", " + "'" + values[i] + "'";
        }
        query += ")";
        Debug.Log(query);
        return ExecuteQuery(query);
    }

    /// <summary>
    /// 返回表的查询结果
    /// </summary>
    /// <returns>The all.</returns>
    /// <param name="Name">Name.</param>
    public DataSet SelectAll(string Name)
    {
        string query = "select * from " + " " + Name;
        return ExecuteQuery(query);
    }
    /// <summary>
    /// 条件查找
    /// </summary>
    /// <returns>The where.</returns>
    /// <param name="tableName">Table name.</param>
    /// <param name="items">Items.</param>
    /// <param name="col">Col.</param>
    /// <param name="operation">Operation.</param>
    /// <param name="values">Values.</param>
    public DataSet SelectWhere(string tableName, string[] items, string[] col, string[] operation, string[] values)
    {
        if (col.Length != operation.Length || operation.Length != values.Length)
        {
            throw new Exception("col.Length != operation.Length != values.Length");
        }
        string query = "SELECT " + items[0];
        for (int i = 1; i < items.Length; ++i)
        {
            query += ", " + items[i];
        }
        query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' ";
        for (int i = 1; i < col.Length; ++i)
        {
            query += " AND " + col[i] + operation[i] + "'" + values[0] + "' ";
        }

        return ExecuteQuery(query);
    }

    /// <summary>
    /// 更新表信息
    /// </summary>
    /// <returns>The into.</returns>
    /// <param name="tableName">Table name.</param>
    /// <param name="cols">Cols.</param>
    /// <param name="colsvalues">Colsvalues.</param>
    /// <param name="selectkey">Selectkey.</param>
    /// <param name="selectvalue">Selectvalue.</param>
    public DataSet UpdateInto(string tableName, string[] cols, string[] colsvalues, string selectkey, string selectvalue)
    {
        string query = "UPDATE " + tableName + " SET " + cols[0] + " = " + colsvalues[0];
        for (int i = 1; i < colsvalues.Length; ++i)
        {
            query += ", " + cols[i] + " =" + colsvalues[i];
        }
        query += " WHERE " + selectkey + " = " + selectvalue + " ";
        return ExecuteQuery(query);
    }

    /// <summary>
    /// 条件删除
    /// </summary>
    /// <param name="tableName">Table name.</param>
    /// <param name="cols">Cols.</param>
    /// <param name="colsvalues">Colsvalues.</param>
    public DataSet Delete(string tableName, string[] cols, string[] colsvalues)
    {
        string query = "DELETE FROM " + tableName + " WHERE " + cols[0] + " = " + colsvalues[0];
        for (int i = 1; i < colsvalues.Length; ++i)
        {
            query += " or " + cols[i] + " = " + colsvalues[i];
        }
        Debug.Log(query);
        return ExecuteQuery(query);
    }

    /// <summary>
    /// 关闭数据库
    /// </summary>
    public void Close()
    {
        if (dbConnection != null)
        {
            dbConnection.Close();
            dbConnection.Dispose();
            dbConnection = null;
        }
    }

    public DataSet ExistOrNot(string sql)
    {
        if (dbConnection.State == ConnectionState.Open)
        {
            Debug.Log(sql);
            DataSet ds = new DataSet();  //表的集合
            try
            {
                MySqlDataAdapter da = new MySqlDataAdapter(sql, dbConnection);
                da.Fill(ds);
            }
            catch (Exception ee)
            {
                throw new Exception("SQL:" + sql + "/n" + ee.Message.ToString());
            }
            finally
            {

            }
            return ds;
        }
        return null;
    }


    /// <summary>
    /// 返回检索结果
    /// </summary>
    /// <param name="sqlString"></param>
    /// <returns></returns>
    public static DataSet ExecuteQuery(string sqlString)
    {
        if (dbConnection .State == ConnectionState.Open)
        {
            DataSet ds = new DataSet();   //表的集合
            try
            {
                MySqlDataAdapter da = new MySqlDataAdapter(sqlString, dbConnection);
                da.Fill(ds);
            }
            catch (Exception ee)
            {
                HintMessage.instance.Message("用户名已存在!");
                throw new Exception("SQL:" + sqlString + "/n" + ee.Message.ToString());
            }
            finally
            {

            }
            return ds;
        }
        return null;
    }

    /// <summary>
    /// 读取数据库
    /// </summary>
    /// <param name="ds"></param>
    public void ReadDs(DataSet ds)
    {
        if (ds!= null)
        {
            DataTable user = ds.Tables[0];
            foreach (DataRow row in user.Rows )
            {
                foreach (DataColumn  colum in user.Columns)
                {
                    Debug.Log(row[colum]);
                }
            }
        }
    }
}
